<?php

namespace console\db;

use ReflectionClass;
use Yii;
use yii\db\ColumnSchemaBuilder;
use yii\db\Exception;
use yii\db\Expression;
use yii\db\Query;
use yii\helpers\ArrayHelper;
use yii\helpers\Console;
use yii\helpers\FileHelper;
use yii\helpers\Inflector;
use yii\helpers\Json;

/**
 * Migration自定义扩展
 *
 * @author emhome <emhome@163.com>
 * @since 1.0
 */
class Migration extends \yii\db\Migration {

    use SchemaBuilderTrait;

    const ENGINE_INNODB = 'InnoDB';
    const ENGINE_MYISAM = 'MyISAM';
    const BATCH_SIZE = 100;
    const BUILD_TABLE_METHOD_PREFIX = 'buildTable';

    /**
     * @event UpDownEvent an event raised before executing a controller action.
     * You may set [[UpDownEvent::isValid]] to be `false` to cancel the action execution.
     */
    const EVENT_BEFORE_UP_DOWN = 'beforeUpDown';

    /**
     * @event UpDownEvent an event raised after executing a controller action.
     */
    const EVENT_AFTER_UP_DOWN = 'afterUpDown';

    /**
     * @var string 临时增加内存数值
     */
    public $memorySize;

    /**
     * @var array 忽略创建的表
     */
    public $ignoreTables = [];

    /**
     * @var string 创建表选项
     */
    public $tableOptions = null;

    /**
     * @var array 移除时表数组
     */
    public $dropTables = [];

    /**
     * @var string 默认使用事务性表存储引擎
     */
    public $tableEngine = self::ENGINE_INNODB;

    /**
     * @var string 默认使用事务性表存储引擎
     */
    public $tableCharset = null;

    /**
     * @var boolean 是否增加备注信息
     */
    public $useComment = YII_DEBUG;

    /**
     * @var boolean 是否增加备注信息
     */
    public static $dataAlias = '@app/data';

    /**
     * @var string 存储过程名称
     */
    public $procedureName = '';

    /**
     * @var bool indicates whether the console output should be compacted.
     * If this is set to true, the individual commands ran within the migration will not be output to the console.
     * Default is false, in other words the output is fully verbose by default.
     * @since 2.0.13
     */
    public $compact = true;

    /**
     * @var string 增量批次表
     */
    public $mirgrationBatchTable = 'migration_batch';

    /**
     * 源数据库连接
     * @return \console\db\Connection 源数据库连接
     */
    public function getOriginalDb() {
        return Yii::$app->transDb;
    }

    /**
     * @inheritdoc
     */
    public function init() {
        parent::init();
        if ($this->tableCharset === null) {
            $this->tableCharset = $this->db->charset;
        }
        if ($this->db->driverName === 'mysql') {
            $this->tableOptions = 'CHARACTER SET ' . $this->tableCharset . ' COLLATE ' . $this->tableCharset . '_general_ci ENGINE=' . $this->tableEngine;
        }
        if (isset(Yii::$app->params['migrate.ignore.tables']) && !empty(Yii::$app->params['migrate.ignore.tables'])) {
            $this->ignoreTables = ArrayHelper::merge($this->ignoreTables, Yii::$app->params['migrate.ignore.tables']);
        }
        if ($this->memorySize) {
            ini_set('memory_limit', $this->memorySize);
        }
    }

    /**
     * @inheritdoc
     */
    public function getDbName($db = null) {
        if (!$db) {
            $db = Yii::$app->getDb();
        }
        return $this->getDsnAttribute('dbname', $db->dsn);
    }

    /**
     * @inheritdoc
     */
    private function getDsnAttribute($name, $dsn) {
        if (preg_match('/' . $name . '=([^;]*)/', $dsn, $match)) {
            return $match[1];
        } else {
            return null;
        }
    }

    /**
     * Builds and executes a SQL statement for dropping an index.
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     */
    public function hasTableSchemaAttribute($table, $attribute = null, $value = null) {
        $query = (new Query())->from('information_schema.statistics')->andWhere([
            'and',
            ['table_schema' => $this->getDbName()],
            ['table_name' => $this->basenameTable($table, true)],
        ]);
        if ($attribute && $value) {
            $query->andFilterWhere([$attribute => $value]);
        }
//        Console::output($query->createCommand()->getRawSql());
        return $query->exists();
    }

    /**
     * 读取数据文件内容
     */
    public function getData($name, $alias = null) {
        $ext = pathinfo($name, PATHINFO_EXTENSION);
        if (!$alias) {
            $alias = static::$dataAlias;
        }
        echo $path = Yii::getAlias($alias) . '/' . $ext . '/' . $name;
        Yii::error($path);
        if (!file_exists($path)) {
            return null;
        }
        if ($ext == 'php') {
            return require $path;
        }
        $data = file_get_contents($path);
        if ($ext == 'json') {
            return Json::decode($data);
        }
        return $data;
    }

    /**
     * 读取数据文件内容
     */
    public function getDatas($path) {
        $path = Yii::getAlias(static::$dataAlias) . '/' . ltrim($path, '/');
        return FileHelper::findFiles($path);
    }

    /**
     * 读取数据文件内容
     */
    public static function loadData($name) {
        $ext = pathinfo($name, PATHINFO_EXTENSION);
        $path = Yii::getAlias(static::$dataAlias) . '/' . $ext . '/' . $name;
        if (!file_exists($path)) {
            return null;
        }
        if ($ext == 'php') {
            return require $path;
        }
        $data = file_get_contents($path);
        if ($ext == 'json') {
            return Json::decode($data);
        }
        return $data;
    }

    /**
     * 预留目标表空间
     * @inheritdoc
     */
    public function getBatchPrevRecord() {
        $query = (new Query())->from('{{%' . $this->mirgrationBatchTable . '}}')->limit(2)->orderBy([
            'id' => SORT_DESC
        ]);
        $records = $query->all();
        if (empty($records)) {
            return null;
        }
        return array_pop($records);
    }

    /**
     * 预留目标表空间
     * @inheritdoc
     */
    public function getBatchIncrementTable($table, $status = 0) {
        $tableName = str_replace(['{', '}', '%'], '', $table);
        $lastBatchId = $this->getTableAutoIncrementId($this->mirgrationBatchTable);
        if (!$lastBatchId) {
            return null;
        }
        $query = (new Query())->from('{{%' . $this->mirgrationBatchTable . '_table}}');
        $query->andWhere([
            'table' => $tableName,
            'batch_id' => $lastBatchId,
        ]);
        if ($status !== false) {
            $query->andWhere([
                'status' => $status,
            ]);
        }
        return $query->one();
    }

    /**
     * @inheritdoc
     */
    public function createTable($table, $columns, $options = null) {
        if ($this->checkTableIgnored($table)) {
            return true;
        }
        $this->dropTables[] = $table;
        if ($options === null) {
            $options = $this->tableOptions;
        }
        if ($this->useComment === false) {
            foreach ($columns as $type) {
                /* @var $type \yii\db\ColumnSchemaBuilder */
                if ($type instanceof ColumnSchemaBuilder && $type->comment !== null) {
                    $type->comment = null;
                }
            }
        }
        parent::createTable($table, $columns, $options);
    }

    /**
     * @inheritdoc
     */
    public function safeUp() {
        $reflectionClass = new ReflectionClass(get_called_class());
        $methods = $reflectionClass->getMethods();
        foreach ($methods as $method) {
            if (strpos($method->name, static::BUILD_TABLE_METHOD_PREFIX) === false) {
                continue;
            }
            $options = call_user_func_array([$this, $method->name], [$this]);
            if (!empty($options) && is_array($options)) {
                $tablename = ArrayHelper::remove($options, 'name');
                if (!$tablename) {
                    $tablename = Inflector::underscore(str_replace(static::BUILD_TABLE_METHOD_PREFIX, '', $method->name));
                }
                ArrayHelper::setValue($options, 'name', $tablename);
                $this->tableBuilder($options);
            }
        }
    }

    /**
     * @inheritdoc
     */
    public function safeDown() {
        $reflectionClass = new ReflectionClass(get_called_class());
        $methods = $reflectionClass->getMethods();
        $dropTables = [];
        foreach ($methods as $method) {
            if (strpos($method->name, static::BUILD_TABLE_METHOD_PREFIX) === false) {
                continue;
            }
            $options = call_user_func_array([$this, $method->name], [$this]);
            if (!empty($options) && is_array($options)) {
                $tablename = ArrayHelper::getValue($options, 'name');
                if (!$tablename) {
                    $tablename = Inflector::underscore(str_replace(static::BUILD_TABLE_METHOD_PREFIX, '', $method->name));
                }
                $subsize = ArrayHelper::getValue($options, 'subsize');
                if ($subsize) {
                    for ($i = 0; $i < $subsize; $i++) {
                        $dropTables[] = $tablename . '_' . $i;
                    }
                } else {
                    $dropTables[] = $tablename;
                }
            }
        }
        krsort($dropTables);
        foreach ($dropTables as $tablename) {
            $this->dropTable($tablename);
        }
    }

    /**
     * @inheritdoc
     */
    public function dropTable($table, $subsize = 0) {
        if ($this->checkTableIgnored($table)) {
            return true;
        }
        if (strpos($table, '{{') === false) {
            $table = '{{%' . $table . '}}';
        }
        $sql = "DROP TABLE IF EXISTS $table;";
        if ($subsize > 0) {
            $pos = strpos($table, '}}');
            if ($pos !== false) {
                $table = substr_replace($table, '_', $pos, 2);
            }
            for ($i = 0; $i < $subsize; $i++) {
                $_table = $table . $i . '}}';
                $sql .= "DROP TABLE IF EXISTS $_table;";
            }
        }
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     * ``` sql
     * DROP TRIGGER IF EXISTS `triggerName`;
     * DELIMITER;
     * CREATE TRIGGER `triggerName` BEFORE UPDATE ON `tablename` FOR EACH ROW BEGIN
     *  SET NEW.weights = (NEW.weight_master+NEW.weight_increase+NEW.weight_float);
     * END;
     * DELIMITER;
     * ```
     */
    public function createTrigger($name, $table, $procedure, $behavior = "BEFORE", $mode = "INSERT") {
        $tablename = $this->basenameTable($table, true);
        $_behavior = strtoupper($behavior);
        $_mode = strtoupper($mode);
        if (!in_array($_behavior, ['BEFORE', 'AFTER'])) {
            Console::output("This trigger's behavior has unsupported.");
            return;
        }
        if (!in_array($_mode, ['INSERT', 'UPDATE', 'DELETE'])) {
            Console::output("This trigger's mode has unsupported.");
            return;
        }

        $sql = implode("\n", [
            "DROP TRIGGER IF EXISTS `$name`;",
            "CREATE TRIGGER `$name` $_behavior $_mode ON `$tablename` FOR EACH ROW BEGIN",
            (string) $procedure,
            "END;",
        ]);
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * Builds and executes a SQL statement for truncating a DB table.
     * @param string $table the table to be truncated. The name will be properly quoted by the method.
     */
    public function truncateTable($table) {
        if ($this->isExistsTable($table)) {
            parent::truncateTable($table);
        }
    }

    /**
     * @inheritdoc
     */
    public function createOrUpdateTableColumns($table, $columns) {
        $tableColumns = $this->db->getTableSchema($table)->columnNames;
        foreach ($columns as $column => $type) {
            if (!in_array($column, $tableColumns)) {
                $this->addColumn($table, $column, $type);
            } else {
                $this->alterColumn($table, $column, $type);
            }
        }
    }

    /**
     * Builds and executes a SQL statement for adding a new DB column.
     * @param string $table the table that the new column will be added to. The table name will be properly quoted by the method.
     * @param string $column the name of the new column. The name will be properly quoted by the method.
     * @param string $type the column type. The [[QueryBuilder::getColumnType()]] method will be invoked to convert abstract column type (if any)
     * into the physical one. Anything that is not recognized as abstract type will be kept in the generated SQL.
     * For example, 'string' will be turned into 'varchar(255)', while 'string not null' will become 'varchar(255) not null'.
     */
    public function createColumn($table, $column, $type) {
        $tableColumns = $this->db->getTableSchema($table)->columnNames;
        if (!in_array($column, $tableColumns)) {
            $this->addColumn($table, $column, $type);
        }
    }

    /**
     * Builds and executes a SQL statement for dropping a DB column.
     * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $column the name of the column to be dropped. The name will be properly quoted by the method.
     */
    public function dropColumn($table, $column) {
        $tableColumns = $this->db->getTableSchema($table)->columnNames;
        if (in_array($column, $tableColumns)) {
            parent::dropColumn($table, $column);
        }
    }

    /**
     * Builds and executes a SQL statement for dropping a DB column.
     * @param string $column the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $type the name of the column to be dropped. The name will be properly quoted by the method.
     */
    public function createOrDropColumn($table, $column, $type = 'char(32)') {
        $tableColumns = $this->db->getTableSchema($table)->columnNames;
        if (in_array($column, $tableColumns)) {
            parent::dropColumn($table, $column);
        } else {
            parent::addColumn($table, $column, $type);
        }
    }

    /**
     * 预留目标表空间
     * @inheritdoc
     */
    public function getTableAutoIncrementId($tableName) {
        $table = $this->basenameTable($tableName, true);
        return (new Query())->from($table)->select(new Expression('max(id)'))->scalar();
    }

    /**
     * Builds and executes a SQL statement for dropping a DB column.
     * @param string $table the table whose column is to be dropped. The name will be properly quoted by the method.
     * @param string $inc the name of the column to be dropped. The name will be properly quoted by the method.
     */
    public function resetAutoIncrement($table, $inc = 1, $absolute = false) {
        $time = $this->beginCommand("reset table $table's auto_increment");
        $now = $this->getTableAutoIncrementId($table);
        $incs = intval($inc) ?: 1;
        if ($absolute) {
            $incs = max($now + 1, $incs);
        } else {
            $incs += $now;
        }
        $sql = "ALTER TABLE {$table} AUTO_INCREMENT=" . ($incs) . ";";
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     */
    public function dropView($view) {
        $sql = "DROP VIEW IF EXISTS `$view`";
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     */
    public function dropProcedure($procedure) {
        $sql = "DROP PROCEDURE IF EXISTS `$procedure`";
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @inheritdoc
     */
    public function dropFunction($procedure) {
        $sql = "DROP FUNCTION IF EXISTS `$procedure`";
        $time = $this->beginCommand($sql);
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * 移除表名中的通配符
     * @param string $name
     */
    protected function basenameTable($name, $prefixReserved = false) {
        $basename = str_replace(['{', '}', '%'], '', $name);
        $tablePrefix = $this->db->tablePrefix;
        if ($tablePrefix && strpos($basename, $tablePrefix) === 0) {
            $basename = substr($basename, strlen($tablePrefix) - 1);
        }
        if ($prefixReserved) {
            return $this->db->tablePrefix . ltrim($basename, '_');
        }
        return $basename;
    }

    /**
     * 移除表名中的通配符
     * @param string $name
     */
    public function getRawTableName($name, $prefixReserved = true) {
        $basename = $this->db->getSchema()->getRawTableName($name);
        if (!$prefixReserved) {
            $tablePrefix = $this->db->tablePrefix;
            if ($tablePrefix && strpos($basename, $tablePrefix) === 0) {
                $basename = substr($basename, strlen($tablePrefix) - 1);
            }
        }
        return $basename;
    }

    /**
     * 外键格式构建器
     * @param string $delete
     * @param string $update
     */
    protected function buildFkClause($delete = '', $update = '') {
        return implode(' ', ['', $delete, $update]);
    }

    /**
     * 检测表是否存在被引用的字段
     * @param string $tablename
     */
    protected function checkTableReferenced($tablename, $field = null) {
        $cache = Yii::$app->cache;
        $databaseName = $this->getDbName();
        $cacheKey = $databaseName . '_KEY_COLUMN_USAGE';
        $data = $cache->get($cacheKey);
        if ($data === false) {
            $query = (new Query())->from('information_schema.KEY_COLUMN_USAGE')->andWhere([
                'and',
                ['referenced_table_schema' => $databaseName],
            ]);
            $columns = $query->all();
            $data = [];
            foreach ($columns as $column) {
                $referenced_table_name = ArrayHelper::getValue($column, 'referenced_table_name');
                $referenced_column_name = ArrayHelper::getValue($column, 'referenced_column_name');
                if ($referenced_table_name && $referenced_column_name) {
                    $data[$referenced_table_name][] = $referenced_column_name;
                }
            }
            $cache->set($cacheKey, $data);
        }
        $basenameTable = $this->basenameTable($tablename, true);
        if ($field) {
            $tableColumns = ArrayHelper::getValue($data, $basenameTable, []);
            return in_array($field, $tableColumns);
        }
        return isset($data[$basenameTable]);
    }

    /**
     * 检测是否忽略的表
     * @param string $tablename
     */
    protected function checkTableIgnored($tablename) {
        $table = $this->basenameTable($tablename);
        if (in_array($table, $this->ignoreTables)) {
            return true;
        }
        return false;
    }

    /**
     * Builds and executes a SQL statement for truncating a DB table.
     * @param string $table the table to be truncated. The name will be properly quoted by the method.
     */
    public function forceTruncateTable($table) {
        if ($this->checkTableReferenced($table)) {
            $this->db->createCommand('SET FOREIGN_KEY_CHECKS = 0;')->execute();
            parent::truncateTable($table);
            $this->db->createCommand('SET FOREIGN_KEY_CHECKS = 1;')->execute();
        } else {
            parent::truncateTable($table);
        }
    }

    /**
     * Builds and executes a SQL statement for dropping an index.
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     */
    public function dropIndex($name, $table) {
        if ($this->hasTableSchemaAttribute($table, 'index_name', $name)) {
            parent::dropIndex($name, $table);
        }
    }

    /**
     * Builds and executes a SQL statement for dropping an index.
     * @param string $name the name of the index to be dropped. The name will be properly quoted by the method.
     * @param string $table the table whose index is to be dropped. The name will be properly quoted by the method.
     */
    public function createIndex($name, $table, $columns, $unique = false) {
        if (!$this->hasTableSchemaAttribute($table, 'index_name', $name)) {
            parent::createIndex($name, $table, $columns, $unique);
        }
    }

    public function batchAddForeignKeys($tableName, $foreignKeys) {
        foreach ($foreignKeys as $name => $options) {
            $foreignColumns = ArrayHelper::getValue($options, 'columns', []);
            $refTable = ArrayHelper::getValue($options, 'refTable');
            if (!$refTable || empty($foreignColumns)) {
                continue;
            }
            $delete = ArrayHelper::getValue($options, 'delete', 'CASCADE');
            $update = ArrayHelper::getValue($options, 'update', 'CASCADE');
            $columns = [];
            $refColumns = [];
            foreach ($foreignColumns as $column => $refColumn) {
                if (is_numeric($column)) {
                    $column = $refColumn;
                }
                $columns[] = $column;
                $refColumns[] = $refColumn;
                if (is_numeric($name)) {
                    $name = 'fk_' . $column . '_' . $refColumn;
                }
            }
            $this->addForeignKey($name, $tableName, $columns, $refTable, $refColumns, $delete, $update);
        }
    }

    /**
     * 批量添加索引
     * @param type $tableName
     * @param type $indexs
     */
    public function batchAddIndexs($tableName, $indexs) {
        foreach ($indexs as $index) {
            if (!isset($index['columns']) || empty($index['columns'])) {
                continue;
            }
            $indexColumns = $index['columns'];
            $defaultIndexName = 'idx_';
            if (is_string($indexColumns)) {
                $defaultIndexName .= $indexColumns;
            } elseif (is_array($indexColumns)) {
                $defaultIndexName .= implode('_', $indexColumns);
            }
            if (strlen($defaultIndexName) > 32) {
                $defaultIndexName = substr($defaultIndexName, 0, 32);
            }
            $indexName = ArrayHelper::remove($index, 'name', $defaultIndexName);
            $unique = ArrayHelper::remove($index, 'unique', false);
            $this->createIndex($indexName, $tableName, $indexColumns, $unique);
        }
    }

    /**
     * 批量添加触发器
     * @param type $tableName
     * @param type $triggers
     */
    public function batchAddTriggers($tableName, $triggers) {
        foreach ($triggers as $trigger) {
            $triggerName = ArrayHelper::getValue($trigger, 'name');
            if (!$triggerName) {
                continue;
            }
            $procedure = ArrayHelper::getValue($trigger, 'sql');
            $behavior = ArrayHelper::getValue($trigger, 'behavior', 'before');
            $mode = ArrayHelper::getValue($trigger, 'mode', 'insert');
            if (empty($procedure)) {
                continue;
            }
            if (is_array($procedure)) {
                $procedure = implode("\n", $procedure);
            }
            $this->createTrigger($triggerName, $tableName, $procedure, $behavior, $mode);
        }
    }

    /**
     * 检测表是否存在
     * @param string $tablename
     */
    protected function isExistsTable($tablename) {
        $isexist = $this->hasTableSchemaAttribute($tablename);
        if (!$isexist) {
            Console::output("This table `{$tablename}` is not exists.");
        }
        return $isexist;
    }

    /**
     * 检测视图是否存在
     * @param string $viewname
     */
    protected function isExistsView($viewname) {
        $isexist = $this->hasTableSchemaAttribute($viewname);
        if (!$isexist) {
            Console::output("This view `{$viewname}` is not exists.");
        }
        return $isexist;
    }

    /**
     * 运行数据表构建
     * @param string $tableName
     * @param array $columns
     * @param array $primaryKeys
     * @param array $indexs
     * @param boolean $comment
     */
    protected function executeTableBuild($tableName, $columns, $primaryKeys = [], $foreignKeys = [], $indexs = [], $triggers = [], $comment = false) {
        if (strpos($tableName, '{{') === false) {
            $tableName = '{{%' . $tableName . '}}';
        }
        if ($this->isExistsTable($tableName)) {
            if (Console::confirm("Table {$tableName} already exists. \n Are you sure Remove?")) {
                $this->dropTable($tableName);
                Console::output("Table {$tableName} already drop.");
            } else {
                Console::output("The current creation operation is ignored.");
                return;
            }
        }
        $created = $this->createTable($tableName, $columns);
        if ($created === false) {
            return null;
        }
        if (!empty($primaryKeys)) {
            foreach ($primaryKeys as $_key) {
                $this->addPrimaryKey($_key, $tableName, $_key);
            }
        }
        if ($this->useComment !== false && $comment) {
            $this->addCommentOnTable($tableName, $comment);
        }
        if (!empty($foreignKeys)) {
            $this->batchAddForeignKeys($tableName, $foreignKeys);
        }
        if (!empty($indexs)) {
            $this->batchAddIndexs($tableName, $indexs);
        }
        if (!empty($triggers)) {
            $this->batchAddTriggers($tableName, $triggers);
        }
    }

    /**
     * 表结构构建器
     * @param array $table
     */
    public function tableBuilder($table) {
        if (!is_array($table) || empty($table)) {
            throw new Exception('This table struction is not array or empty!');
        }
        if (!isset($table['name']) || !is_string($table['name'])) {
            throw new Exception('This table name is not exists or string!');
        }
        if (!isset($table['columns']) || empty($table['columns'])) {
            throw new Exception('This table columns is not exists or empty!');
        }

        $tableName = $table['name'];
        $columns = $table['columns'];

        $primaryKeys = ArrayHelper::remove($table, 'key', []);
        $foreignKeys = ArrayHelper::remove($table, 'foreignKeys', []);
        $indexs = ArrayHelper::remove($table, 'index', []);
        $triggers = ArrayHelper::remove($table, 'trigger', []);
        $comment = ArrayHelper::remove($table, 'comment', false);
        $subsize = ArrayHelper::remove($table, 'subsize', false);
        if ($subsize !== false) {
            for ($i = 0; $i < $subsize; $i++) {
                $this->executeTableBuild($tableName . '_' . $i, $columns, $primaryKeys, $foreignKeys, $indexs, $triggers, $comment);
            }
        } else {
            $this->executeTableBuild($tableName, $columns, $primaryKeys, $foreignKeys, $indexs, $triggers, $comment);
        }
    }

    public function viewBuilder($view) {
        if (!is_array($view) || empty($view)) {
            throw new Exception('This view struction is not array or empty!');
        }
        if (!isset($view['name']) || !is_string($view['name'])) {
            throw new Exception("This view's name not exists or datatype is not string!");
        }
        if (!isset($view['tables']) || empty($view['tables'])) {
            throw new Exception('This view tables is not exists or empty!');
        }
        $viewName = $view['name'];
        $tables = $view['tables'];
        $where = $view['where'];

        if ($this->isExistsView($viewName)) {
            if (Console::confirm("View {$viewName} already exists. \n Are you sure Remove?")) {
                $this->dropView($viewName);
                Console::output("View {$viewName} already drop.");
            } else {
                Console::output("The current creation operation is ignored.");
                return;
            }
        }

        $query = new Query();

        $selects = [];
        $from = [];
        foreach ($tables as $table => $columns) {
            $from[] = $table;
            foreach ($columns as $alias => $col) {
                $_field = "$table.`$col`";
                if (is_string($alias)) {
                    $_field .= ' as `' . $alias . '`';
                }
                $selects[] = $_field;
            }
        }
        $query->select($selects)->from($from)->where($where);

        $time = $this->beginCommand("create view $viewName");
        $this->db->createCommand()->createView($viewName, $query)->execute();
        $this->endCommand($time);
    }

    public function procedureBuilder($procedure) {
        if (!is_array($procedure) || empty($procedure)) {
            throw new Exception('This procedure struction is not array or empty!');
        }
        if (!isset($procedure['name']) || !is_string($procedure['name'])) {
            throw new Exception("This procedure's name not exists or datatype is not string!");
        }
        if (!isset($procedure['sql']) || !is_string($procedure['sql']) || empty($procedure['sql'])) {
            throw new Exception("This procedure's sql not exists or datatype is not string!");
        }
        $procedureName = $procedure['name'];
        $sql = "CREATE PROCEDURE `$procedureName`";
        $params = [];
        if (isset($procedure['params']) && !empty($procedure['params'])) {
            foreach ($procedure['params'] as $param) {
                if (!is_array($param)) {
                    continue;
                }
                if (!isset($param['name']) || empty($param['name'])) {
                    continue;
                }
                $temp = [];
                $mode = isset($param['mode']) ? strtoupper($param['mode']) : '';
                if (in_array($mode, ['IN', 'OUT', 'INOUT'])) {
                    $temp[] = $mode;
                }
                $temp[] = '`' . $param['name'] . '`';
                $type = isset($param['type']) ? $param['type'] : '';
                if ($type) {
                    $size = isset($param['size']) ? intval($param['size']) : 0;
                    if ($size) {
                        $type .= '(' . $size . ')';
                    }
                    $temp[] = $type;
                }
                $params[] = implode(' ', $temp);
            }
        }
        if (!empty($params)) {
            $sql .= '(' . implode(',', $params) . ')';
        } else {
            $sql .= '()';
        }
        $sql .= "BEGIN\n";
        $sql .= (string) $procedure['sql'];
        $sql .= "\nEND;";

        $time = $this->beginCommand("create procedure $procedureName");
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    public function functionBuilder($procedure) {
        if (!is_array($procedure) || empty($procedure)) {
            throw new Exception('This procedure struction is not array or empty!');
        }
        if (!isset($procedure['name']) || !is_string($procedure['name'])) {
            throw new Exception("This procedure's name not exists or datatype is not string!");
        }
        if (!isset($procedure['sql']) || !is_string($procedure['sql']) || empty($procedure['sql'])) {
            throw new Exception("This procedure's sql not exists or datatype is not string!");
        }
        $procedureName = $procedure['name'];
        $sql = "CREATE FUNCTION `$procedureName`";
        $params = [];
        if (isset($procedure['params']) && !empty($procedure['params'])) {
            foreach ($procedure['params'] as $param) {
                if (!is_array($param)) {
                    continue;
                }
                if (!isset($param['name']) || empty($param['name'])) {
                    continue;
                }
                $temp = [];
                $temp[] = '`' . $param['name'] . '`';
                $type = isset($param['type']) ? $param['type'] : '';
                if ($type) {
                    $size = isset($param['size']) ? intval($param['size']) : 0;
                    if ($size) {
                        $type .= '(' . $size . ')';
                    }
                    $temp[] = $type;
                }
                $params[] = implode(' ', $temp);
            }
        }
        if (!empty($params)) {
            $sql .= '(' . implode(',', $params) . ')';
        } else {
            $sql .= '()';
        }

        if (isset($procedure['return']) && !empty($procedure['return'])) {
            $returns = $procedure['return'];
            $temp = [
                'RETURNS',
            ];
            $type = isset($returns['type']) ? $returns['type'] : 'varchar';
            $size = isset($returns['size']) ? intval($returns['size']) : 1000;
            $temp[] = $type . '(' . $size . ')';
            $temp[] = 'CHARSET';
            $temp[] = $this->tableCharset;
            $sql .= ' ' . implode(' ', $temp);
        }

        $sql .= "\nBEGIN\n";
        $sql .= (string) $procedure['sql'];
        $sql .= "\nEND;";

        $time = $this->beginCommand("create function $procedureName");
        $this->db->createCommand($sql)->execute();
        $this->endCommand($time);
    }

    /**
     * @param integer $unionId 是否替换数据,如果为真, 则创建REPLACE INTO sql语句 (Only Mysql)
     * @param \yii\db\Connection $db
     * @see \yii\db\Command::batchInsert();
     */
    public static function callFunction($name, $params = [], $db = null) {
        if (!$db) {
            $db = Yii::$app->getDb();
        }
        $bindParams = [];
        if (!empty($params)) {
            $params = (array) $params;
            foreach ($params as $key => $vls) {
                $_prefix = ':';
                if (is_numeric($key)) {
                    $_prefix .= 'p';
                }
                $bindParams[$_prefix . $key] = $vls;
            }
        }
        $function = $name . '(' . implode(',', array_keys($bindParams)) . ')';
        $expression = new Expression($function, $bindParams);
        return (new Query())->select($expression)->scalar($db);
    }

    /**
     * {@inheritdoc}
     */
    public static function callProcedure($name, $params = [], $db = null) {
        if (!$db) {
            $db = Yii::$app->getDb();
        }
        $sql = "CALL $name";
        $tempKeys = [];
        $inParams = [];
        $outFileds = [];
        if (!empty($params)) {
            foreach ($params as $key => $vls) {
                if (is_int($key)) {
                    $outkey = '@' . ltrim($vls, '@');
                    $tempKeys[] = $outkey;
                    $outFileds[] = $outkey;
                } elseif (strpos($key, '@') === 0) {
                    $tempKeys[] = $key;
                    $outFileds[] = $key;
                } elseif (strpos($key, ':') === 0) {
                    $tempKeys[] = $key;
                    $inParams[$key] = $vls;
                } else {
                    $tempKeys[] = ':' . $key;
                    $inParams[':' . $key] = $vls;
                }
            }
        }
        if (!empty($tempKeys)) {
            $sql .= '(' . implode(',', $tempKeys) . ')';
        }
        $sql .= ';';
        $query = $db->createCommand($sql)->bindValues($inParams);
        if (empty($outFileds)) {
            return $query;
        }
        $query->execute();
        return $db->createCommand('SELECT ' . implode(',', $outFileds) . ';')->queryScalar();
    }

    /**
     * 批量sql插入数据
     * @param string $tableName
     * @param array $columns
     * @param string $path
     */
    public function sqlBatchInsert($tableName, $columns, $path) {
        $this->truncateTable('{{%' . $tableName . '}}');
        $string = file_get_contents($path);
        $format = 'INSERT INTO `%s` (`' . implode('`, `', $columns) . '`) VALUES';
        $delimiter = sprintf($format, $tableName);
        $insertSchema = sprintf($format, $this->db->tablePrefix . $tableName);
        $data = explode($delimiter, $string);
        foreach ($data as $_sql) {
            if ($_sql) {
                $this->execute($insertSchema . $_sql);
            }
        }
    }

    /**
     * 批量更新数据
     * @param string $leftTable
     * @param string $rightTable
     * @param array $on
     * @param array $set
     * @param string $where
     * @param string $db
     */
    public function innerJoinBatchUpdate($leftTable, $rightTable, $on = [], $set = [], $where = null, $db = null) {
        if (!$db) {
            $db = Yii::$app->getDb();
        }
        $ons = [];
        foreach ($on as $lf => $rf) {
            $ons[] = implode('=', [
                $db->quoteColumnName($leftTable . '.' . $lf),
                $db->quoteColumnName($rightTable . '.' . $rf),
            ]);
        }
        $onCondition = implode(' AND ', $ons);
        $baseQuery = (new Query())->from($leftTable)->innerJoin($rightTable, $onCondition);
        $updateQuery = clone $baseQuery;
        $tempSql = $updateQuery->andHaving($set)->createCommand($db)->getRawSql();
        $sql = str_ireplace([
            'SELECT * FROM',
            'HAVING',
        ], [
            'UPDATE',
            'SET',
        ], $tempSql);
        if (!empty($where)) {
            $andWhereQuery = clone $baseQuery;
            $andWhereSql = $andWhereQuery->andWhere($where)->createCommand($db)->getRawSql();
            $temp = explode('WHERE ', $andWhereSql);
            if ($temp[1]) {
                $sql .= ' WHERE ' . $temp[1];
            }
        }
        $db->createCommand($sql)->execute();
    }

    /**
     * This parameter will be ignored if not supported by the DBMS.
     * Creates a tinyint column.
     * @param Query $query 
     * @param callable $callback 
     * @param mixed $extends
     * @param \yii\db\Connection $db
     * @param int|null $count column size definition i.e. the maximum string length.
     * @since 2.0.8
     */
    public function handleProgress(Query $query, callable $callback, $extends = null, $db = null, $count = null) {
        if ($count === null) {
            $count = (new Query())->from($query)->count('*', $db);
        }
        if (!$count) {
            return;
        }
        $i = 1;
        $size = static::BATCH_SIZE;
        $pages = ceil($count / $size);
        Console::startProgress($i, $pages, 'Updating objects: ', false);
        foreach ($query->batch($size, $db) as $rows) {
            $i++;
            call_user_func_array($callback, [$this, $rows, $extends]);
            Console::updateProgress($i, $pages);
        }
        Console::endProgress("update done.");
    }

    /**
     * @inheritdoc
     */
    public function transfer($row, $fields) {
        $data = [];
        foreach ($fields as $field) {
            if (is_string($field)) {
                $data[] = $row[$field];
            } elseif (is_callable($field)) {
                $data[] = call_user_func($field, $row);
            }
        }
        return $data;
    }

    /**
     * @inheritdoc
     */
    public function afterTransfer($tableName) {
        Console::output('Transfer table: ' . $tableName . ' done.');
    }

    /**
     * @inheritdoc
     */
    public function validateTransfer(&$data) {
        if (!empty($data)) {
            return true;
        }
        return false;
    }

    /**
     * @inheritdoc
     */
    public function dumpDivideText($data, $char = '=') {
        list($width) = Console::getScreenSize();
        $line = str_pad($char, $width, $char);
        Console::output($line);
        if (is_array($data)) {
            Console::output(print_r($data, true));
        } else {
            Console::output($data);
        }
        Console::output($line);
    }

    /**
     * @inheritdoc
     */
    public function up() {
        if (!$this->beforeUpDown('up')) {
            return false;
        }
        $result = parent::up();
        return $this->afterUpDown('up', $result);
    }

    /**
     * @inheritdoc
     */
    public function down() {
        if (!$this->beforeUpDown('down')) {
            return false;
        }
        $result = parent::down();
        return $this->afterUpDown('down', $result);
    }

    /**
     * This method is invoked right before an action within this module is executed.
     *
     * The method will trigger the [[EVENT_BEFORE_ACTION]] event. The return value of the method
     * will determine whether the action should continue to run.
     *
     * In case the action should not run, the request should be handled inside of the `beforeAction` code
     * by either providing the necessary output or redirecting the request. Otherwise the response will be empty.
     *
     * If you override this method, your code should look like the following:
     *
     * ```php
     * public function beforeUpDown($action)
     * {
     *     if (!parent::beforeUpDown($action)) {
     *         return false;
     *     }
     *
     *     // your custom code here
     *
     *     return true; // or false to not run the action
     * }
     * ```
     *
     * @param Action $action the action to be executed.
     * @return bool whether the action should continue to be executed.
     */
    public function beforeUpDown($action) {
        $event = new UpDownEvent($action);
        $this->trigger(self::EVENT_BEFORE_UP_DOWN, $event);
        return $event->isValid;
    }

    /**
     * This method is invoked right after an action within this module is executed.
     *
     * The method will trigger the [[EVENT_AFTER_ACTION]] event. The return value of the method
     * will be used as the action return value.
     *
     * If you override this method, your code should look like the following:
     *
     * ```php
     * public function afterUpDown($action, $result)
     * {
     *     $result = parent::afterUpDown($action, $result);
     *     // your custom code here
     *     return $result;
     * }
     * ```
     *
     * @param Action $action the action just executed.
     * @param mixed $result the action return result.
     * @return mixed the processed action result.
     */
    public function afterUpDown($action, $result) {
        $event = new UpDownEvent($action);
        $event->result = $result;
        $this->trigger(self::EVENT_AFTER_UP_DOWN, $event);
        return $event->result;
    }

}
